﻿Imports DataGridViewAutoFilter
Imports Microsoft.Office.Interop



Public Class Acad_DatasetsForm

    Class GAGFields
        Public FieldName As String
        Public FieldCode As Integer
        Public Period_ID As Integer
    End Class

    Class GAGFormulas
        Public FieldName As String
        Public FieldCode As Integer
        Public Formula As String
    End Class

    Class NotesClass
        Public Note_Code As String
        Public TheDate As DateTime
        Public TheUser As String
        Public Note As String
    End Class

    Class CellClass
        Public RID As String
        Public FieldName As String
        Public FieldCode As Integer
        Public Value As Object
        Public Period_ID As Integer
    End Class

    Class EditClass
        Public RID As String
        Public FieldCode As Integer
        Public Period_ID As Integer
    End Class

    Dim Edited As Boolean = False
    Dim EditedCells As New List(Of EditClass)

    Public Fields As New List(Of GAGFields)
    Dim Formulas As New List(Of GAGFormulas)

    Dim TheNotes As New List(Of NotesClass)
    Dim CurrentNoteNumber As Integer

    Dim CurrentCell As New CellClass
    Dim CurrentDataset As Integer = 0

    Private Sub LoadTerritoriesBox()

        LoadComboBox(TerritoryBox, "select '<All>' as Territory_Code, '<All>' as Territory_Name Union Select Territory_Code, Territory_Name from Territories", "Territory_Code", "Territory_Name")
    End Sub

    Private Sub LoadLABox()
      
        If TerritoryBox.Items.Count = 0 Then Exit Sub

        If TerritoryBox.SelectedValue.ToString = "<All>" Then
            LoadComboBox(LABox, "select '<All>' as LA_Code, '<All>' as LA_name Union select LA_Code, LA_Name from dbo.TerritoryJoin tj, dbo.Local_Authorities la where la.Region_Code = tj.Region_Code", "LA_Code", "LA_Name")
        Else
            LoadComboBox(LABox, "select '<All>' as LA_Code, '<All>' as LA_name Union select LA_Code, LA_Name from dbo.TerritoryJoin tj, dbo.Local_Authorities la where la.Region_Code = tj.Region_Code and tj.Territory_Code = '" & TerritoryBox.SelectedValue.ToString & "'", "LA_Code", "LA_Name")
        End If

    End Sub

    Private Sub Acad_DatasetsForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'load period box
        If LoadPeriodBox(PeriodBox) = True Then
            PeriodBox.SelectedValue = CurrentPeriodID
        Else
            MessageBox.Show("An error occured loading the period box", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        LoadTerritoriesBox()
        LoadLABox()

        NotesContainer.SplitterDistance = Math.Round(Me.Width / 2, 0)
        MainContainer.SplitterDistance = Math.Round((Me.Height / 4) * 3, 0)

        LoadDatasetBox()

    End Sub


    Private Sub LoadDatasetBox()

        Dim sql As String

        sql = "select Dataset_Code, Dataset_Description from dbo.Acad_GAG_Datasets UNION Select 999 as Dataset_Code, 'Summary' as Dataset_Description"

        If LoadComboBox(DatasetBox, sql, "Dataset_Code", "Dataset_Description") = False Then
            MessageBox.Show("An error occured loading the datasets", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If
    End Sub

    Private Function FixFormula(ByVal Formula As String) As String

        'step through the formula and make sure that all the periods will exist other wise replace it with a 0

        Dim S As String = Formula
        Dim Field As String = ""
        Dim PeriodNum As Integer

        While S.Contains("]")

            'find the field
            Field = Mid(S, S.IndexOf("[") + 1, S.IndexOf("]") - S.IndexOf("[") + 1)
            PeriodNum = Mid(Field, Field.IndexOf("P") + 2, (Field.IndexOf("]") - Field.IndexOf("P")) - 1)

            If GetPeriodByNum(PeriodBox.SelectedValue, PeriodNum) = 0 Then
                Formula = Formula.Replace(Field, "0")
            End If

            S = S.Replace(Field, "")

        End While

        Return Formula
    End Function

    Private Function casting(ByVal Type As String)
        Select Case Type
            Case "INT"
                casting = "int"
            Case "PERCENT"
                casting = "decimal(18,2)"
            Case "MONEY"
                casting = "decimal(18,2)"
            Case "DP1"
                casting = "decimal(18,1)"
            Case "DP2"
                casting = "decimal(18,2)"
            Case "DP3"
                casting = "decimal(18,3)"
            Case "DP4"
                casting = "decimal(18,4)"
            Case "DP5"
                casting = "decimal(18,5)"
            Case "DP6"
                casting = "decimal(18,6)"
            Case "DATE"
                casting = "datetime"
            Case Else
                casting = ""
        End Select
    End Function

    Private Function BuildSQL() As String
        Try
            Dim test As String = DatasetBox.SelectedValue.ToString
        Catch ex As Exception
            Return ""
            Exit Function
        End Try

        Dim Dataset As String = DatasetBox.SelectedValue.ToString
        Dim PeriodID As String = PeriodBox.SelectedValue.ToString
        Dim sql As String = ""
        Dim mainsql As String = ""
        Dim TheFieldName As String = ""
        Dim SubFieldName As String = ""
        Dim Case_Period_ID As Integer
        Dim PeriodsList As New List(Of String)
        Dim r As Integer
        Dim PeriodExists As Boolean = False

        Dim TheView As DataView
        Dim dr As DataRowView

        Fields.Clear()
        Formulas.Clear()
        EditedCells.Clear()

        If Dataset = "System.Data.DataRowView" Then
            Return ""
            Exit Function
        End If

        TheView = LoadDataView("select * from Acad_GAG_Templates where Dataset_Code = " & Dataset & " order by Field_Order")


        'start with the sub query to get the values in
        sql = "Select d.RID "

        PeriodsList.Clear()

        For Each dr In TheView
            TheFieldName = dr.Item("Field_Name").ToString
            Case_Period_ID = GetPeriodByNum(PeriodBox.SelectedValue, dr.Item("Period"))

            If Case_Period_ID <> 0 Then
                'only do this column if it has a period

                TheFieldName = TheFieldName.Replace("[YEAR]", Period_Name(Case_Period_ID))
                TheFieldName = TheFieldName.Replace("[YEAR-1]", Period_Name(GetPeriodByNum(Case_Period_ID, -1)))
                TheFieldName = TheFieldName.Replace("[YEAR+1]", Period_Name(GetPeriodByNum(Case_Period_ID, 1)))
                SubFieldName = dr.Item("Field_Code").ToString & "P" & dr.Item("Period").ToString

                'add the period to the list if it does not exist yet
                If PeriodsList.Count <> 0 Then
                    PeriodExists = False
                    For r = 0 To PeriodsList.Count - 1
                        If PeriodsList(r) = Case_Period_ID Then
                            PeriodExists = True
                        End If
                    Next

                    If Not PeriodExists Then
                        PeriodsList.Add(Case_Period_ID)
                    End If
                Else
                    PeriodsList.Add(Case_Period_ID)
                End If

                If dr.Item("Formula") = "INPUT" Then

                    If dr.Item("Type").ToString = "YN" Then
                        sql = sql & ", case sum(case when d.Period_ID = " & Case_Period_ID & " and d.Field_Code = " & dr.Item("Field_Code").ToString & " then Value else 0 end) when 0 then 'N' else 'Y' end as [" & SubFieldName & "] "
                    Else

                        If dr.Item("Type").ToString = "DATE" Then
                            sql = sql & ",cast(sum(case when d.Period_ID = " & Case_Period_ID & " and d.Field_Code = " & dr.Item("Field_Code").ToString & " then coalesce(Value,0) else 0 end) as DateTime) as [" & SubFieldName & "] "
                        Else
                            sql = sql & ",sum(case when d.Period_ID = " & Case_Period_ID & " and d.Field_Code = " & dr.Item("Field_Code").ToString & " then coalesce(Value,0) else 0 end) as [" & SubFieldName & "] "
                        End If


                        'If casting(dr.Item("Type").ToString) = "" Then
                        ' sql = sql & ",sum(case when d.Period_ID = " & Case_Period_ID & " and d.Field_Code = " & dr.Item("Field_Code").ToString & " then coalesce(Value,0) else 0 end) as [" & SubFieldName & "] "
                        ' Else
                        ' sql = sql & ",cast(sum(case when d.Period_ID = " & Case_Period_ID & " and d.Field_Code = " & dr.Item("Field_Code").ToString & " then coalesce(Value,0) else 0 end) as " & casting(dr.Item("Type").ToString) & ") as [" & SubFieldName & "] "
                        'End If
                    End If

                Dim fielditm As New GAGFields


                'Add field code and field name to the list

                fielditm.FieldCode = dr.Item("Field_Code")
                fielditm.FieldName = TheFieldName
                fielditm.Period_ID = Case_Period_ID

                Fields.Add(fielditm)

                'also add the notes field
                sql = sql & ",sum(case when d.Period_ID = " & Case_Period_ID & " and d.Field_Code = " & dr.Item("Field_Code").ToString & " then coalesce(NotesCount,0) else 0 end) as [" & SubFieldName & "_Notes] "
            Else

                Dim formitm As New GAGFormulas
                'Add field code and field name to the list

                formitm.FieldCode = dr.Item("Field_Code")
                formitm.FieldName = TheFieldName
                formitm.Formula = FixFormula(dr.Item("Formula").ToString)

                Formulas.Add(formitm)
            End If

            End If

        Next

        sql = sql & "from Acad_GAG_Data d " & _
                    "left outer join " & _
                    "(select Dataset_code, Period_ID, RID, Field_Code, count(TheDate) as NotesCount from Acad_GAG_Notes group by Dataset_Code, Period_ID, RID, Field_Code) notes " & _
                    "on (d.dataset_code = notes.dataset_code and d.period_ID = notes.period_id and d.RID = notes.rid and d.field_code = notes.field_code) " & _
                    "where d.Dataset_Code = " & Dataset & " and d.Period_ID in (" & String.Join(",", PeriodsList.ToArray) & ") and d.Status = 'Approved' group by d.RID"

        sql = "(" & sql & ") sub"
        'now do the final query to get formulas and headers in

        mainsql = "Select p.RID, p.LA_Name as [Local Authority], p.UAI, p.UPIN, cast(LA_Code as nvarchar(50)) + cast(Establishment_Number as nvarchar(50)) as ESTAB, p.Provider_Name as [Provider Name], case when coalesce(Converter,0) = 0 then 'Sponsored' else 'Converter' end as [Academy Type] "
        For Each dr In TheView

            TheFieldName = dr.Item("Field_Name").ToString
            SubFieldName = dr.Item("Field_Code").ToString & "P" & dr.Item("Period").ToString
            Case_Period_ID = GetPeriodByNum(PeriodBox.SelectedValue, dr.Item("Period"))

            If Case_Period_ID <> 0 Then

                'add a year if needed
                TheFieldName = TheFieldName.Replace("[YEAR]", Period_Name(Case_Period_ID))
                TheFieldName = TheFieldName.Replace("[YEAR-1]", Period_Name(GetPeriodByNum(Case_Period_ID, -1)))
                TheFieldName = TheFieldName.Replace("[YEAR+1]", Period_Name(GetPeriodByNum(Case_Period_ID, 1)))

                If dr.Item("Formula") = "INPUT" Then

                    mainsql = mainsql & ",[" & SubFieldName & "] as [" & TheFieldName & "]"
                    mainsql = mainsql & ",coalesce([" & SubFieldName & "_Notes],0) as [" & TheFieldName & "_Notes]"
                Else
                    mainsql = mainsql & ",(" & FixFormula(dr.Item("SQL").ToString) & ") as [" & TheFieldName & "]"
                    'If casting(dr.Item("Type").ToString) <> "" Then
                    ' mainsql = mainsql & ",cast((" & FixFormula(dr.Item("SQL").ToString) & ") as " & casting(dr.Item("Type").ToString) & ") as [" & TheFieldName & "]"
                    ' Else
                    ' mainsql = mainsql & ",(" & FixFormula(dr.Item("SQL").ToString) & ") as [" & TheFieldName & "]"
                    'End If
                End If
            End If
        Next

        Dim filter As String

        filter = TheFilter()

        Dim UAIsql As String = ""

        'If DatasetBox.SelectedValue = 500 Then
        '    UAIsql = " and p.UAI in (select UAI from Acad_Additional_Provider_Info where Funding_ID = 'CON') "
        'Else
        '    UAIsql = " and p.UAI in (select UAI from Acad_Additional_Provider_Info where Funding_ID <> 'CON') "
        'End If

        mainsql = mainsql & " From " & sql & " right outer join All_Provider_Fields p on (p.RID = sub.RID) where p.Provider_Type_Code = 'Acade' and p.UPIN in (select UPIN from provider_funding_streams where Period_ID = " & PeriodBox.SelectedValue & " and Budget_ID = 'YPLRE') " & UAIsql & filter & " order by p.LA_Name, p.UAI"
        Return mainsql
    End Function

    Private Function TheFilter() As String
        Dim filter As String = ""

        If TerritoryBox.SelectedValue.ToString <> "<All>" Then
            filter = " and p.Territory_Code = '" & TerritoryBox.SelectedValue.ToString & "' "
        End If

        If LABox.SelectedValue.ToString <> "<All>" Then
            filter = filter & " and p.LA_Code = '" & LABox.SelectedValue.ToString & "' "
        End If

        Return filter
    End Function

    Private Sub EnableButtons(ByVal TrueOrFalse As Boolean)
        SaveButton.Enabled = TrueOrFalse
        ImportButton.Enabled = TrueOrFalse
        CreateTemplateButton.Enabled = TrueOrFalse
    End Sub

    Public Function CreatSummarytable(ByVal Filter As String, ByVal Period_ID As Integer, ByVal FieldNames As Boolean) As Boolean
        Dim TheView As DataView
        Dim Sql As String

        ShowLoadingForm()
        'get all the RIDs
        Sql = "Select RID from All_provider_fields p where p.Provider_Type_Code = 'Acade' and p.UPIN in (select UPIN from provider_funding_streams where period_ID = " & PeriodBox.SelectedValue & " and Budget_ID = 'YPLRE') " & Filter

        TheView = LoadDataView(Sql)

        DataGrid.DataSource = Nothing

        ProgressBarForm.Show()
        ProgressBarForm.TheProgressBar.Minimum = 0
        ProgressBarForm.TheProgressBar.Maximum = TheView.Count
        ProgressBarForm.TheProgressBar.Step = 1
        ProgressBarForm.Refresh()


        For Each rec In TheView

            If FieldNames = True Then
                Sql = "select " & _
                "p.RID, p.LA_Name as [Local Authority], p.UAI, p.UPIN, p.Provider_Name as [Provider Name], case when coalesce(Converter,0) = 0 then 'Sponsored' else 'Converter' end as [Academy Type]  " & _
                "              	,cast(A.A1 as int) as [USLA Y1 to Y6]  " & _
                "                	,cast(A.A2 as int) as [USLA Y7 to Y11]  " & _
                "                	,cast(A.A3 as int) as OSLA  " & _
                "                	,cast(A5 as money) as [School Budget Share (incl. Post 16 Funding)]  " & _
                "                	,cast(Sch_All_Post16_Allocation as money) as [Post 16 Funding]  " & _
                "                	,cast(A6 as money) as [LA Central Spend Equivalent Grant] " & _
                "                	,cast(A7 as money) as [Insurance]  " & _
                "                	,cast(A8 as money) as [Teacher Threshold]  " & _
                "                	,cast(A9 as money) as [Mainstreamed Grants]  " & _
                "                	,cast(A10 as money) as [Start Up Grant Part a]  " & _
                "                	,cast(A11 as money) as [Start Up Grant Part b Formulaic]  " & _
                "                	,cast(A12 as money) as [Start Up Grant Part b Assessment]  " & _
                "                	,EAG_Amount as [Total EAG]  " & _
                "                	,cast(coalesce(SF.Standards_Fund,0) as money) as [Standards Fund]  " & _
                "                	,cast(A5+A6+A7+A8+A9+A10+A11+A12+EAG_Amount+coalesce(SF.Standards_Fund,0) as money) as [TOTAL]  " & _
                "                from  " & _
                "                   All_Provider_Fields P " & _
                "                   left outer join  " & _
                "                	VW_Acad_Annex_A_Main A  " & _
                "                   on (P.RID = A.RID) " & _
                "                	left outer join  " & _
                "                	(select    " & _
                "                		Period_ID  " & _
                "                		,RID  " & _
                "                		,sum(case when Field_Code in (100,110) then coalesce(Value,0) else 0 end) as Standards_Fund  " & _
                "                	from dbo.Acad_GAG_Data  " & _
                "                	where Dataset_Code = 170  " & _
                "                		and Status = 'Approved' and Period_ID = " & Period_ID.ToString & _
                "                	Group by  " & _
                "                		Period_ID, RID) SF on (A.Period_ID = SF.Period_ID and A.RID = SF.RID)  " & _
                "                 " & _
                "                where  " & _
                "                	A.period_id = " & Period_ID.ToString & _
                "                   and p.RID = '" & rec.Item("RID").ToString & "' "
            Else
                Sql = "select " & _
                "p.RID, p.LA_Name as [Local Authority], p.UAI, p.UPIN, p.Provider_Name as [Provider Name], case when coalesce(Converter,0) = 0 then 'Sponsored' else 'Converter' end as [Academy Type]  " & _
                "              	,cast(A.A1 as int) as [A1]  " & _
                "                	,cast(A.A2 as int) as [A2]  " & _
                "                	,cast(A.A3 as int) as [A3]  " & _
                "                	,cast(A5 as money) as [A5]  " & _
                "                	,cast(Sch_All_Post16_Allocation as money) as [Sch_All_Post16_Allocation]  " & _
                "                	,cast(A6 as money) as [A6] " & _
                "                	,cast(A7 as money) as [A7]  " & _
                "                	,cast(A8 as money) as [A8]  " & _
                "                	,cast(A9 as money) as [A9]  " & _
                "                	,cast(A10 as money) as [A10]  " & _
                "                	,cast(A11 as money) as [A11]  " & _
                "                	,cast(A12 as money) as [A12]  " & _
                "                	,EAG_Amount as [EAG_Amount]  " & _
                "                	,cast(coalesce(SF.Standards_Fund,0) as money) as [Standards_Fund]  " & _
                "                	,cast(A5+A6+A7+A8+A9+A10+A11+A12+EAG_Amount+coalesce(SF.Standards_Fund,0) as money) as [TOTAL]  " & _
                "                from  " & _
                "                   All_Provider_Fields P " & _
                "                   left outer join  " & _
                "                	VW_Acad_Annex_A_Main A  " & _
                "                   on (P.RID = A.RID) " & _
                "                	left outer join  " & _
                "                	(select    " & _
                "                		Period_ID  " & _
                "                		,RID  " & _
                "                		,sum(case when Field_Code in (100,110) then coalesce(Value,0) else 0 end) as Standards_Fund  " & _
                "                	from dbo.Acad_GAG_Data  " & _
                "                	where Dataset_Code = 170  " & _
                "                		and Status = 'Approved' and Period_ID = " & Period_ID.ToString & _
                "                	Group by  " & _
                "                		Period_ID, RID) SF on (A.Period_ID = SF.Period_ID and A.RID = SF.RID)  " & _
                "                 " & _
                "                where  " & _
                "                	A.period_id = " & Period_ID.ToString & _
                "                   and p.RID = '" & rec.Item("RID").ToString & "' "
            End If

            

            'if this is the first record then drop and create the table

            If ProgressBarForm.TheProgressBar.Value = 0 Then
                RunNonQuery("drop table Acad_Summary_" & CurrentUser.Name)

                Sql = "select * into Acad_Summary_" & CurrentUser.Name & " from (" & Sql & ") a"
                RunNonQuery(Sql)
            Else
                Sql = "insert into Acad_Summary_" & CurrentUser.Name & " " & Sql
                RunNonQuery(Sql)
            End If


            ProgressBarForm.TheProgressBar.PerformStep()
            ProgressBarForm.Refresh()

        Next

        ProgressBarForm.Close()

        HideLoadingForm()

        If TheView.Count <> 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Private Sub LoadSummaryData()

        If TerritoryBox.SelectedValue.ToString = "<All>" And LABox.SelectedValue.ToString = "<All>" Then
            If MessageBox.Show("WARNING:  The summary will take very long if you run it for all providers.  Are you sure you want to continue?", "WARNING", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If
        End If

        Job("Loading Dataset...")
        BePatientLabel.Visible = True
        Me.Refresh()

        EnableButtons(False)


        Dim filter As String

        filter = TheFilter()


        If CreatSummarytable(filter, PeriodBox.SelectedValue.ToString, True) Then
            DataGrid.DataSource = LoadDataView("Select * from Acad_Summary_" & CurrentUser.Name)

            DataGrid.ReadOnly = True

            DataGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText

            DataGrid.Columns("RID").Visible = False
            DataGrid.Columns("RID").Frozen = True
            DataGrid.Columns("RID").ReadOnly = True


            DataGrid.Columns("Local Authority").Frozen = True
            DataGrid.Columns("Local Authority").Visible = True
            DataGrid.Columns("Local Authority").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            DataGrid.Columns("Local Authority").ReadOnly = True


            DataGrid.Columns("UAI").Frozen = True
            DataGrid.Columns("UAI").Visible = True
            DataGrid.Columns("UAI").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
            DataGrid.Columns("UAI").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            DataGrid.Columns("UAI").ReadOnly = True


            DataGrid.Columns("UPIN").Frozen = True
            DataGrid.Columns("UPIN").Visible = True
            DataGrid.Columns("UPIN").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
            DataGrid.Columns("UPIN").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            DataGrid.Columns("UPIN").ReadOnly = True


            DataGrid.Columns("Provider Name").Frozen = True
            DataGrid.Columns("Provider Name").Visible = True
            DataGrid.Columns("Provider Name").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            DataGrid.Columns("Provider Name").ReadOnly = True

            DataGrid.Columns("Academy Type").Frozen = False
            DataGrid.Columns("Academy Type").Visible = True
            'DataGrid.Columns("Academy Type").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
            DataGrid.Columns("Academy Type").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            DataGrid.Columns("Academy Type").ReadOnly = True

            If DataGrid.Rows.Count <> 0 Then
                For i As Integer = 0 To DataGrid.Columns.Count - 1

                    If DataGrid.Item(i, 0).Value.GetType.ToString = "System.Int32" Then
                        DataGrid.Columns(i).DefaultCellStyle.Format = "#,0"
                    End If

                    If DataGrid.Item(i, 0).Value.GetType.ToString = "System.Decimal" Then
                        DataGrid.Columns(i).DefaultCellStyle.Format = "C"
                    End If

                Next
            End If
        End If

        BePatientLabel.Visible = False

    End Sub

    Private Sub LoadGrid()
        If Edited Then
            If CurrentDataset <> 999 Then
                If MessageBox.Show("Would you like to save changes before continuing?", "Save changes?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                    Savedata()
                End If
            End If
        End If

        If DatasetBox.SelectedValue Is Nothing Then Exit Sub

        If DatasetBox.SelectedValue.ToString = "System.Data.DataRowView" Then Exit Sub

        CurrentDataset = DatasetBox.SelectedValue

        If CurrentDataset = 999 Then
            LoadSummaryData()
            Exit Sub
        End If

        Job("Loading Dataset...")
        ShowLoadingForm()
        DataGrid.ReadOnly = False
        EnableButtons(True)

        Me.Cursor = Cursors.WaitCursor

        CurrentCell.FieldCode = 0
        CurrentCell.FieldName = ""
        CurrentCell.RID = ""
        CurrentCell.Value = ""

        CurrentNoteNumber = -1

        Dim sql As String
        Dim Case_Period_ID As Integer

        DataGrid.AutoGenerateColumns = True
        DataGrid.Columns.Clear()

        Edited = False

        sql = BuildSQL()

        If sql = "" Then Exit Sub

        'TheBS.Filter = ""

        'TheBS.DataSource = LoadDataTable(sql)

        'Dim col As DataGridViewColumn

        'For Each col In DataGrid.Columns
        '    col.HeaderCell = New DataGridViewAutoFilterColumnHeaderCell(col.HeaderCell)

        'Next

        'DataGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText

        DataGrid.DataSource = LoadDataView(sql)


        'set some formatting
        sql = "select * from Acad_GAG_Templates where Dataset_Code = " & DatasetBox.SelectedValue.ToString & " order by Field_Order"
        Dim TheView As DataView
        Dim dr As DataRowView

        TheView = LoadDataView(sql)

        Dim FieldName As String

        For Each dr In TheView

            FieldName = dr.Item("Field_Name").ToString
            Case_Period_ID = GetPeriodByNum(PeriodBox.SelectedValue, dr.Item("Period"))

            If Case_Period_ID <> 0 Then


                '   FieldName = FieldName.Replace("[YEAR]", PeriodBox.Text)
                FieldName = FieldName.Replace("[YEAR]", Period_Name(Case_Period_ID))
                FieldName = FieldName.Replace("[YEAR-1]", Period_Name(GetPeriodByNum(Case_Period_ID, -1)))
                FieldName = FieldName.Replace("[YEAR+1]", Period_Name(GetPeriodByNum(Case_Period_ID, 1)))

                Select Case dr.Item("Type").ToString
                    Case "DATE"
                        DataGrid.Columns(FieldName).DefaultCellStyle.Format = "d"
                    Case "MONEY"
                        DataGrid.Columns(FieldName).DefaultCellStyle.Format = "C"
                    Case "PERCENT"
                        DataGrid.Columns(FieldName).DefaultCellStyle.Format = "P"
                    Case "INT"
                        DataGrid.Columns(FieldName).DefaultCellStyle.Format = "#,0"
                    Case "YN"
                        'add a drop down box for Yes and No
                        Dim index As Integer

                        ' find the location of the column
                        index = DataGrid.Columns.IndexOf(DataGrid.Columns(FieldName))

                        ' remove the existing column
                        DataGrid.Columns.RemoveAt(index)

                        ' create a new custom column
                        Dim ComboCol As New DataGridViewComboBoxColumn
                        ComboCol.Items.Clear()
                        ComboCol.Items.Add("Y")
                        ComboCol.Items.Add("N")
                        ComboCol.Name = FieldName
                        ComboCol.DataPropertyName = FieldName

                        ' some more tweaking
                        ComboCol.SortMode = DataGridViewColumnSortMode.Automatic

                        ' insert the new column at the same location
                        DataGrid.Columns.Insert(index, ComboCol)

                    Case Else
                        DataGrid.Columns(FieldName).DefaultCellStyle.Format = "N"
                End Select

                'Hide and show and size columns

                If dr.Item("Formula").ToString <> "INPUT" Then
                    'format the formula fields
                    DataGrid.Columns(FieldName).Visible = True
                    DataGrid.Columns(FieldName).ReadOnly = True
                    DataGrid.Columns(FieldName).DefaultCellStyle.BackColor = Color.LightGray
                    DataGrid.Columns(FieldName).DefaultCellStyle.Font = New Font(Control.DefaultFont, FontStyle.Bold)
                Else
                    'hide the notes fields
                    DataGrid.Columns(FieldName & "_Notes").Visible = False
                    DataGrid.Columns(FieldName).ReadOnly = False
                End If
            End If

        Next


        DataGrid.Columns("RID").Visible = False
        DataGrid.Columns("RID").Frozen = True
        DataGrid.Columns("RID").ReadOnly = True


        DataGrid.Columns("Local Authority").Frozen = True
        DataGrid.Columns("Local Authority").Visible = True
        DataGrid.Columns("Local Authority").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        DataGrid.Columns("Local Authority").ReadOnly = True


        DataGrid.Columns("UAI").Frozen = True
        DataGrid.Columns("UAI").Visible = True
        DataGrid.Columns("UAI").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
        DataGrid.Columns("UAI").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGrid.Columns("UAI").ReadOnly = True


        DataGrid.Columns("UPIN").Frozen = True
        DataGrid.Columns("UPIN").Visible = True
        DataGrid.Columns("UPIN").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
        DataGrid.Columns("UPIN").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGrid.Columns("UPIN").ReadOnly = True

        DataGrid.Columns("ESTAB").Frozen = True
        DataGrid.Columns("ESTAB").Visible = True
        DataGrid.Columns("ESTAB").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
        DataGrid.Columns("ESTAB").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        DataGrid.Columns("ESTAB").ReadOnly = True


        DataGrid.Columns("Provider Name").Frozen = True
        DataGrid.Columns("Provider Name").Visible = True
        DataGrid.Columns("Provider Name").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        DataGrid.Columns("Provider Name").ReadOnly = True

        DataGrid.Columns("Academy Type").Frozen = False
        DataGrid.Columns("Academy Type").Visible = True
        'DataGrid.Columns("Academy Type").AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader
        DataGrid.Columns("Academy Type").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        DataGrid.Columns("Academy Type").ReadOnly = True

        HideLoadingForm()
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub DatasetBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DatasetBox.SelectedIndexChanged
        ResetViews()

    End Sub

    Private Sub ResetViews()
        DataGrid.DataSource = Nothing
        NotesBox.ReadOnly = True
        TheToolbar.Enabled = False

    End Sub

    Private Sub DataGrid_DataError(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles DataGrid.DataError
        MessageBox.Show("The value you have entered is not correct for this data type, please try again.", "Wrong data type entered", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Sub

    Private Function FieldName(ByVal FieldCode As Integer, ByVal Period_ID As Integer) As String
        Dim r As Integer

        If Fields.Count = 0 Then
            Return ""
            Exit Function
        End If

        For r = 0 To Fields.Count - 1
            If Fields(r).FieldCode = FieldCode And Fields(r).Period_ID = Period_ID Then
                Return Fields(r).FieldName
                Exit Function
            End If
        Next

        Return ""
    End Function

    Public Function FieldCode(ByVal FieldName As String) As Integer
        Dim r As Integer

        If Fields.Count = 0 Then
            Return 0
            Exit Function
        End If

        For r = 0 To Fields.Count - 1
            If Fields(r).FieldName = FieldName Then
                Return Fields(r).FieldCode
                Exit Function
            End If
        Next

        Return 0
    End Function

    Public Function FieldPeriod(ByVal FieldName As String) As Integer
        Dim r As Integer

        If Fields.Count = 0 Then
            Return 0
            Exit Function
        End If

        For r = 0 To Fields.Count - 1
            If Fields(r).FieldName = FieldName Then
                Return Fields(r).Period_ID
                Exit Function
            End If
        Next

        Return 0
    End Function

    Private Sub RefreshFormulas(ByVal row As Integer)

        If Formulas.Count = 0 Then Exit Sub

        Dim itm As New GAGFormulas
        Dim Value As Double

        For Each itm In Formulas

            Dim TheFormula As String

            TheFormula = itm.Formula

            'now that you have the formula replace it with the field codes
            'find the [] and replace them

            While TheFormula.Contains("[")
                'find the start and end of the first field and replace it
                Dim s, e As Integer
                Dim TheCode As String
                Dim TheName As String
                Dim PeriodNum As Integer
                Dim column As DataGridViewColumn


                s = TheFormula.IndexOf("[")
                e = TheFormula.IndexOf("]")

                If s = -1 Or e = -1 Then
                    'something is wrong so exit
                    Exit Sub
                Else
                    TheCode = TheFormula.Substring(s + 1, (e - s) - 1)

                    'find the column and replace it with its value
                    PeriodNum = TheCode.Substring(4, (Len(TheCode) - TheCode.IndexOf("P")) - 1)

                    TheName = FieldName(TheCode.Substring(0, 3), GetPeriodByNum(PeriodBox.SelectedValue, PeriodNum))

                    If TheName <> "" Then
                        For Each column In DataGrid.Columns
                            If column.Name = TheName Then
                                Try
                                    Value = DataGrid.Item(DataGrid.Columns(column.Name).Index, row).Value
                                Catch ex As Exception
                                    Value = 0
                                End Try

                                TheFormula = TheFormula.Replace("[" & TheCode & "]", Value.ToString)

                            End If
                        Next
                    Else
                        'it could not find the field so remove it from the formula
                        TheFormula = TheFormula.Replace("[" & TheCode & "]", "0")
                    End If


                End If

            End While


            If TheFormula.Contains("/0") Then
                Value = Nothing
            Else
                Value = New DataTable().Compute(TheFormula, "")
            End If

            DataGrid.Item(DataGrid.Columns(itm.FieldName).Index, row).Value = Value

        Next
    End Sub


    Private Sub CellEdited(ByVal RID As String, ByVal FieldCode As Integer, ByVal Period_ID As Integer)

        'first check if this field already exists
        Dim r As Integer

        If EditedCells.Count <> 0 Then
            For r = 0 To EditedCells.Count - 1
                If EditedCells(r).RID = RID And EditedCells(r).FieldCode = FieldCode And EditedCells(r).Period_ID = Period_ID Then
                    'dont do anything, it is already listed
                    Exit Sub
                End If
            Next
        End If

        'ok so we made it here, so we need to just add it
        Dim itm As New EditClass

        itm.RID = RID
        itm.FieldCode = FieldCode
        itm.Period_ID = Period_ID

        EditedCells.Add(itm)

    End Sub

    Private Sub RemoveCellEdit(ByVal RID As String, ByVal FieldCode As Integer, ByVal Period_ID As Integer)
        Dim r As Integer

        If EditedCells.Count <> 0 Then
            For r = 0 To EditedCells.Count - 1
                If EditedCells(r).RID = RID And EditedCells(r).FieldCode = FieldCode And EditedCells(r).Period_ID = Period_ID Then
                    EditedCells.RemoveAt(r)
                    Exit Sub
                End If
            Next
        End If
    End Sub

    Private Function IsCellEdited(ByVal RID As String, ByVal FieldCode As Integer, ByVal Period_ID As Integer) As Boolean
        If EditedCells.Count = 0 Then
            Return False
            Exit Function
        End If

        Dim r As Integer

        For r = 0 To EditedCells.Count - 1
            If EditedCells(r).RID = RID And EditedCells(r).FieldCode = FieldCode And EditedCells(r).Period_ID = Period_ID Then
                Return True
                Exit Function
            End If
        Next

        Return False

    End Function

   

    Private Sub DataGrid_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGrid.CellEnter


        CurrentCell.FieldName = DataGrid.Columns(e.ColumnIndex).Name

        If DataGrid.Columns(e.ColumnIndex).ReadOnly = True Then
            CurrentCell.FieldCode = 0
        Else
            CurrentCell.FieldCode = FieldCode(CurrentCell.FieldName)
            CurrentCell.Period_ID = FieldPeriod(CurrentCell.FieldName)
        End If

        CurrentCell.RID = DataGrid.Item(DataGrid.Columns("RID").Index, e.RowIndex).Value
        CurrentCell.Value = DataGrid.Item(e.ColumnIndex, e.RowIndex).Value

        UpdateHistoryView()
        UpdateNotesView()
      

    End Sub

    Private Sub UpdateNotesView()
        Dim value As Integer

        Dim notesfield As String

        'clear the notes
        NotesBox.Text = ""
        NotesLabel.Text = "Notes"
        NotesCountLabel.Text = "0 of 0"
        CurrentNoteNumber = -1

        TheNotes.Clear()

        notesfield = DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name & "_Notes"
        Try
            value = DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value
        Catch ex As Exception
            'there are no notes for this field
            NotesBox.ReadOnly = True
            Exit Sub
        End Try

        NotesBox.ReadOnly = False

        If value = 0 Then Exit Sub


        'load the first note
        Dim sql As String

        sql = "select * from Acad_GAG_Notes where Dataset_Code = " & DatasetBox.SelectedValue.ToString & _
              " and Period_ID = " & CurrentCell.Period_ID & " and RID = '" & CurrentCell.RID & "' and Field_Code = " & CurrentCell.FieldCode & " order by TheDate desc"

        Dim theview As DataView

        theview = LoadDataView(sql)

        'load the list
        Dim r As DataRowView

        For Each r In theview
            Dim itm As New NotesClass

            itm.Note_Code = r.Item("Note_Code").ToString
            itm.Note = r.Item("Note").ToString
            itm.TheDate = r.Item("TheDate")
            itm.TheUser = Trim(r.Item("User_Name").ToString)

            TheNotes.Add(itm)

        Next

        LoadNote(0)

    End Sub

    Private Sub LoadNote(ByVal NoteNumber As Integer)
        If TheNotes.Count = 0 Then Exit Sub

        NotesCountLabel.Text = NoteNumber + 1 & " of " & TheNotes.Count

        NotesBox.Text = TheNotes(NoteNumber).Note
        NotesLabel.Text = FormatDateTime(TheNotes(NoteNumber).TheDate, DateFormat.LongDate) & " " & FormatDateTime(TheNotes(NoteNumber).TheDate, DateFormat.ShortTime) & " - " & TheNotes(NoteNumber).TheUser

        If TheNotes(NoteNumber).TheUser = CurrentUser.Name Then
            DeleteNoteBut.Enabled = True

            If NoteNumber = 0 Then
                NotesBox.ReadOnly = False
            Else
                NotesBox.ReadOnly = True
            End If
        Else
            DeleteNoteBut.Enabled = False
            NotesBox.ReadOnly = True
        End If

        CurrentNoteNumber = NoteNumber

    End Sub

    Private Sub UpdateHistoryView()
        HistoryList.Items.Clear()
        HistoryLabel.Text = "History"
        If DataGrid.SelectedCells.Count = 0 Then Exit Sub

        If DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).ReadOnly = True Then Exit Sub

        If CurrentCell.FieldCode = 0 Then Exit Sub

        HistoryLabel.Text = DataGrid.Item(DataGrid.Columns("Provider Name").Index, DataGrid.CurrentRow.Index).Value & " - " & CurrentCell.FieldName

        Dim sql As String
        Dim TheView As DataView

        sql = "select * from Acad_GAG_Data where Dataset_Code = " & DatasetBox.SelectedValue.ToString & " and Period_ID = " & CurrentCell.Period_ID & " and RID = '" & CurrentCell.RID & "' and Field_Code = " & CurrentCell.FieldCode & " order by TheDate desc"

        TheView = LoadDataView(sql)

        Dim itm As ListViewItem
        Dim str(3) As String
        Dim rec As DataRowView

        For Each rec In TheView
            str(0) = FormatDateTime(rec.Item("TheDate"), DateFormat.ShortDate) & " " & FormatDateTime(rec.Item("TheDate"), DateFormat.ShortTime)
            str(1) = Trim(rec.Item("User_Name").ToString)

            If IsDBNull(rec.Item("Value")) Then
                str(2) = "Blank"
            Else
                str(2) = Format(rec.Item("Value"), "#,0.######")
            End If

            itm = New ListViewItem(str)

            HistoryList.Items.Add(itm)

        Next

    End Sub

    Private Sub DataGrid_CellPainting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGrid.CellPainting

        If DatasetBox.SelectedValue Is Nothing Then Exit Sub

        If DatasetBox.SelectedValue.ToString = "999" Then Exit Sub

        If e.RowIndex = -1 Then
            e.Handled = False
            Exit Sub
        End If

        If e.ColumnIndex = -1 Then
            e.Handled = False
            Exit Sub
        End If

        If DataGrid.Item(e.ColumnIndex, e.RowIndex).ReadOnly = True Then
            e.Handled = False
            Exit Sub
        End If

        If DataGrid.Columns(e.ColumnIndex).Name.Contains("_Notes") Then
            e.Handled = False
            Exit Sub
        End If


        e.Paint(e.ClipBounds, (DataGridViewPaintParts.All))


        'Edit section
        Try

            Dim celRect As New Rectangle(e.CellBounds.X, e.CellBounds.Y, e.CellBounds.Width - 2, e.CellBounds.Height - 2)
            Dim Period As Integer

            Period = FieldPeriod(DataGrid.Columns(e.ColumnIndex).Name)

            If IsCellEdited(DataGrid.Item(DataGrid.Columns("RID").Index, e.RowIndex).Value, FieldCode(DataGrid.Columns(e.ColumnIndex).Name), Period) Then
                e.Graphics.DrawRectangle(Pens.Blue, celRect)
                e.Handled = True
            End If

            '  If DataGrid.Item(e.ColumnIndex, e.RowIndex).Tag = "Edited" Then
            'e.Graphics.DrawRectangle(Pens.Blue, celRect)
            'e.Handled = True
            'End If
        Catch ex As Exception

        End Try


        'Notes Section
        Try
            If DataGrid.Item(DataGrid.Columns(e.ColumnIndex).Name & "_Notes", e.RowIndex).Value = 0 Then
                e.Handled = True
                Exit Sub
            End If
        Catch ex As Exception
            'this cell has no notes column so exit the sub
            e.Handled = True
            Exit Sub
        End Try

        Dim newRect As New Rectangle(e.CellBounds.X, e.CellBounds.Y, 8, 8)


        Try
            e.Graphics.FillRectangle(Brushes.Yellow, newRect)
            e.Graphics.DrawRectangle(Pens.Black, newRect)

            e.Handled = True
        Finally


        End Try


    End Sub

    Private Sub Savedata()
        Dim TheColumn As DataGridViewColumn
        Dim TheRow As DataGridViewRow

        Dim DatasetCode As Integer = CurrentDataset
        Dim RID As String
        Dim sql As String
        Dim TheFieldCode As Integer
        Dim ThePeriod As Integer
        Dim TheValue As String
        Dim TheCellValue As Object

        If CurrentDataset = 999 Then Exit Sub

        If DataGrid.DataSource.ToString = "" Then Exit Sub

        Job("Saving Dataset...")
        ShowLoadingForm()


        'DataGridViewAutoFilterTextBoxColumn.RemoveFilter(DataGrid)


        For Each TheRow In DataGrid.Rows
            For Each TheColumn In DataGrid.Columns

                If TheColumn.Visible = True And TheColumn.ReadOnly = False Then

                    'only save if there is an edit
                    RID = DataGrid.Item(DataGrid.Columns("RID").Index, TheRow.Index).Value
                    TheFieldCode = FieldCode(TheColumn.Name)
                    ThePeriod = FieldPeriod(TheColumn.Name)

                    If IsCellEdited(RID, TheFieldCode, ThePeriod) Then

                        TheFieldCode = FieldCode(TheColumn.Name)

                        If IsDBNull(DataGrid.Item(TheColumn.Index, TheRow.Index).Value) Then
                            TheValue = "null"
                        Else
                            Select Case DataGrid.Item(TheColumn.Index, TheRow.Index).Value.ToString.ToUpper
                                Case "Y"
                                    TheValue = 1
                                Case "N"
                                    TheValue = 0
                                Case Else

                                    TheCellValue = DataGrid.Item(TheColumn.Index, TheRow.Index).Value

                                    If TheCellValue.GetType().ToString.Contains("Date") Then
                                        TheValue = TheCellValue.ToOADate - 2
                                    Else
                                        TheValue = TheCellValue.ToString
                                    End If

                            End Select
                        End If

                        'first set the older values to history
                        sql = "update Acad_GAG_Data set Status = 'History' where Dataset_Code = " & DatasetCode & " and Period_ID = " & ThePeriod & " and RID = '" & RID & "' and Field_Code = " & TheFieldCode & " and Status = 'Approved'"
                        RunNonQuery(sql)

                        'now save the value
                        sql = "insert into Acad_GAG_Data (Dataset_Code, Period_ID, RID, Field_Code, TheDate, User_Name, Status, Value) values(" & _
                            DatasetCode.ToString & ", " & ThePeriod & ", '" & RID & "', " & TheFieldCode & ", " & Now.ToOADate - 2 & ", '" & CurrentUser.Name & "', 'Approved', " & TheValue & ")"
                        RunNonQuery(sql)

                        RemoveCellEdit(RID, TheFieldCode, ThePeriod)
                    End If
                End If

            Next
        Next
        Edited = False

        Job("Dataset saved.")
        HideLoadingForm()

    End Sub

    Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to Save Datasets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Save Datasets", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Savedata()

    End Sub


    Private Sub Acad_DatasetsForm_Deactivate(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Deactivate
        If CurrentDataset = 999 Then Exit Sub

        If Edited Then
            If MessageBox.Show("Would you like to save changes before exiting?", "Save changes?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                Savedata()
            End If
        End If
    End Sub



    Private Sub PreviousNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PreviousNoteBut.Click
        If CurrentNoteNumber = -1 Then Exit Sub
        If CurrentNoteNumber = 0 Then Exit Sub

        LoadNote(CurrentNoteNumber - 1)

    End Sub

    Private Sub NextNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextNoteBut.Click
        If CurrentNoteNumber = -1 Then Exit Sub
        If CurrentNoteNumber = TheNotes.Count - 1 Then Exit Sub

        LoadNote(CurrentNoteNumber + 1)
    End Sub

    Private Sub DeleteNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteNoteBut.Click
        'can I delete you?

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to Delete Notes." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Delete Notes", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        If CurrentNoteNumber = -1 Then Exit Sub

        If TheNotes(CurrentNoteNumber).TheUser = CurrentUser.Name Then

            'only allow deletion if this is the last note (which is the first one)
            If CurrentNoteNumber = 0 Then

                If MessageBox.Show("Are you sure you want to delete this note?", "Delete note?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then Exit Sub

                'ok delete the note
                Dim sql As String


                sql = "delete from Acad_GAG_Notes where Note_Code = '" & TheNotes(CurrentNoteNumber).Note_Code & "'"
                RunNonQuery(sql)

                'reload the cell
                'first update the notes field
                Dim notesfield As String
                Dim value As Integer

                notesfield = DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name & "_Notes"
                Try
                    value = DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value
                    value = value - 1
                    DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value = value

                Catch ex As Exception
                    'there are no notes for this field

                    Exit Sub
                End Try

                UpdateNotesView()

            Else
                MessageBox.Show("You cannot delete this note, because it has been archived", "Cannot delete note", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If

        Else
            MessageBox.Show("You cannot delete this note, because it does not belong to you", "Cannot delete note", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub NewNoteBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewNoteBut.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to Add Notes." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Add Notes", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        NotesCountLabel.Text = "New"

        NotesBox.Text = ""
        NotesBox.ReadOnly = False

        NotesLabel.Text = FormatDateTime(Now, DateFormat.LongDate) & " " & FormatDateTime(Now, DateFormat.ShortTime) & " - " & CurrentUser.Name

        NotesBox.Focus()
    End Sub

    Private Sub SaveNote()
        If NotesCountLabel.Text = "New" Or NotesCountLabel.Text = "Edit" Then

            Dim sql As String

            'lets delete the note first and then we add it back again (but only if we are editing)
            If NotesCountLabel.Text = "Edit" Then
                If CurrentNoteNumber <> -1 Then
                    sql = "delete from Acad_GAG_Notes where Note_Code = '" & TheNotes(CurrentNoteNumber).Note_Code & "'"
                    RunNonQuery(sql)
                End If
            End If


            If Trim(NotesBox.Text) = "" Then Exit Sub

            Dim ADateNum As Double
            Dim ADate As DateTime
            Dim NoteCode As String

            ADate = Now
            ADateNum = ADate.ToOADate - 2

            NoteCode = Mid(CurrentUser.Name, 1, 3) & Format(ADate, "ddMMyyyyHHmmss")

            sql = "insert into Acad_GAG_Notes (Dataset_Code, Period_ID, RID, Field_Code, TheDate, User_Name, Note, Note_Code) Values (" & _
                DatasetBox.SelectedValue & ", " & CurrentCell.Period_ID & ", '" & CurrentCell.RID & "', " & CurrentCell.FieldCode & ", " & ADateNum & ", '" & CurrentUser.Name & "', '" & RemoveBadChars(NotesBox.Text) & "', '" & NoteCode & "')"
            RunNonQuery(sql)

            Dim value As Integer
            Dim notesfield As String

            notesfield = DataGrid.Columns(DataGrid.CurrentCell.ColumnIndex).Name & "_Notes"
            Try
                value = DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value
                value = value + 1
                DataGrid.Item(DataGrid.Columns(notesfield).Index, DataGrid.CurrentRow.Index).Value = value

            Catch ex As Exception
                'there are no notes for this field

                Exit Sub
            End Try

            If IsDBNull(DataGrid.CurrentCell.Value) Then
                DataGrid.CurrentCell.Value = 0
            End If

            'save a blank field to the values table if there is no field yet
            If DataGrid.CurrentCell.Value = 0 Then
                'first set the older values to history
                sql = "update Acad_GAG_Data set Status = 'History' where Dataset_Code = " & DatasetBox.SelectedValue.ToString & " and Period_ID = " & CurrentCell.Period_ID & " and RID = '" & CurrentCell.RID & "' and Field_Code = " & CurrentCell.FieldCode & " and Status = 'Approved'"
                RunNonQuery(sql)

                'delete any other stupid nulls
                sql = "delete from Acad_GAG_Data where value is null and  Dataset_Code = " & DatasetBox.SelectedValue.ToString & " and Period_ID = " & CurrentCell.Period_ID & " and RID = '" & CurrentCell.RID & "' and Field_Code = " & CurrentCell.FieldCode
                RunNonQuery(sql)

                sql = "insert into Acad_GAG_Data (Dataset_Code, Period_ID, RID, Field_Code, TheDate, User_Name, Status, Value) values(" & _
                             DatasetBox.SelectedValue.ToString & ", " & CurrentCell.Period_ID & ", '" & CurrentCell.RID & "', " & CurrentCell.FieldCode & ", " & Now.ToOADate - 2 & ", '" & CurrentUser.Name & "', 'Approved', 0)"
                RunNonQuery(sql)
            End If

            UpdateNotesView()

        End If
    End Sub

    Private Sub NotesBox_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NotesBox.Leave
        SaveNote()
    End Sub

    Private Sub NotesBox_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles NotesBox.KeyUp
        If NotesBox.ReadOnly = True Then Exit Sub

        If NotesCountLabel.Text = "New" Then Exit Sub

        NotesCountLabel.Text = "Edit"
    End Sub


    Private Sub DataGrid_CellValidated(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGrid.CellValidated

        If DatasetBox.SelectedValue.ToString = "999" Then Exit Sub

        Dim TheValue As Object

        If IsDBNull(DataGrid.Item(e.ColumnIndex, e.RowIndex).Value) Then
            TheValue = DBNull.Value
        Else
            TheValue = DataGrid.Item(e.ColumnIndex, e.RowIndex).Value
        End If

        'sort out the nulls

        'both might be
        If IsDBNull(TheValue) And IsDBNull(CurrentCell.Value) Then Exit Sub

        If IsDBNull(CurrentCell.Value) Then
            If Not IsDBNull(TheValue) Then
                CellEdited(CurrentCell.RID, CurrentCell.FieldCode, CurrentCell.Period_ID)
                Edited = True
                RefreshFormulas(e.RowIndex)
                Exit Sub
            End If
        End If


        If IsDBNull(TheValue) Then
            If Not IsDBNull(CurrentCell.Value) Then
                CellEdited(CurrentCell.RID, CurrentCell.FieldCode, CurrentCell.Period_ID)
                Edited = True
                RefreshFormulas(e.RowIndex)
                Exit Sub
            End If
        End If

        Try
            If CurrentCell.Value <> TheValue Then
                CellEdited(CurrentCell.RID, CurrentCell.FieldCode, CurrentCell.Period_ID)
                Edited = True
                RefreshFormulas(e.RowIndex)
            End If
        Catch ex As Exception
            'something went wrong with the compare
        End Try

    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTemplateButton.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to Create Templates." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Create Templates", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        FolderDialog.Description = "Select the folder where you want to save the template"
        With FolderDialog
            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                CreateTemplate(FolderDialog.SelectedPath)
            End If
        End With
    End Sub

    Sub CreateTemplate(ByVal Folder As String)
        ShowLoadingForm()

        'xl stuff
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim xlRng As Excel.Range

        xlApp = New Excel.ApplicationClass

        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Name = Mid(DatasetBox.Text, 1, 31)

        'Add the Columns
        xlWorkSheet.Cells(1, 1) = "Local Authority"
        xlWorkSheet.Cells(1, 2) = "UAI"
        xlWorkSheet.Cells(1, 3) = "Provider Name"
        xlWorkSheet.Cells(1, 4) = "Academy Type"
        xlWorkSheet.Cells(1, 5) = "ESTAB"

        ' DataGridViewAutoFilterTextBoxColumn.RemoveFilter(DataGrid)

        Dim col As DataGridViewColumn
        Dim c As Integer

        c = 6

        For Each col In DataGrid.Columns

            If col.ReadOnly = False Then
                If Not col.Name.Contains("_Notes") Then
                    xlWorkSheet.Cells(1, c) = col.Name
                    c = c + 1
                End If
            End If
        Next

        'Add the Provider data
        Dim r As Integer

        If DataGrid.Rows.Count <> 0 Then
            For r = 2 To DataGrid.Rows.Count + 1
                xlWorkSheet.Cells(r, 1) = DataGrid.Item(DataGrid.Columns("Local Authority").Index, r - 2).Value
                xlWorkSheet.Cells(r, 2) = DataGrid.Item(DataGrid.Columns("UAI").Index, r - 2).Value
                xlWorkSheet.Cells(r, 3) = Trim(DataGrid.Item(DataGrid.Columns("Provider Name").Index, r - 2).Value)
                xlWorkSheet.Cells(r, 4) = Trim(DataGrid.Item(DataGrid.Columns("Academy Type").Index, r - 2).Value)
                xlWorkSheet.Cells(r, 5) = DataGrid.Item(DataGrid.Columns("ESTAB").Index, r - 2).Value
            Next
        End If

        xlRng = xlWorkSheet.Range("1:1")
        xlRng.WrapText = True

        xlRng = xlWorkSheet.Range("C:C")
        xlRng.ColumnWidth = 35

        xlRng = xlWorkSheet.Range("A1:IV" & DataGrid.Rows.Count)
        xlRng.Font.Size = 10

        xlRng = xlWorkSheet.Range("D2")
        xlRng.Select()
        xlWorkSheet.Application.ActiveWindow.FreezePanes = True


        Dim FileName As String
        FileName = DatasetBox.Text
        FileName = FileName & " - " & Format(Now, "ddMMyyyyHHmmss")
        xlWorkBook.SaveAs(Folder & "\" & FileName)
        xlWorkBook.Close(False)

        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        HideLoadingForm()
        MessageBox.Show("The template has been created", "Template created", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub ToolStripSplitButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportButton.Click

        If UserFormCheck(Me.Name) <> "W" Then
            MessageBox.Show("Currently you do not have permission to import datasets." & vbNewLine & _
                            "Contact TheStore.EFA@education.gsi.gov.uk to change permissions.", "Import Datasets", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If


        'reset
        With Acad_ImportWizardDialog

            .ButtonPanel.Enabled = True
            .NextBut.Enabled = True
            .Backbut.Enabled = False
            .TheTabs.SelectedIndex = 0
            .FileBox.Text = ""
            .IgnoreRadio.Checked = True
            .AnalyzeBar.Value = 0
            .ProgressLabel.Text = ""
            .StartBut.Enabled = True

        End With

        If Acad_ImportWizardDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            LoadGrid()
        End If


    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FindButton.Click
        FindDialog.Show()

        With FindDialog
            .TheGrid = DataGrid

        End With
    End Sub

    

    Private Sub Acad_DatasetsForm_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyUp

        If e.Control And e.KeyCode = Keys.F Then
            FindDialog.Show()

            With FindDialog
                .TheGrid = DataGrid

            End With
        End If

       
    End Sub

  

    Private Sub TerritoryBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoryBox.SelectedIndexChanged
        ResetViews()
        LoadLABox()
    End Sub

   

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        NotesBox.ReadOnly = False
        TheToolbar.Enabled = True
        LoadGrid()
        DataGrid.Focus()

    End Sub

    Private Sub LABox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LABox.SelectedIndexChanged
        ResetViews()

    End Sub

    Private Sub PeriodBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodBox.SelectedIndexChanged
        ResetViews()

    End Sub
End Class